package com.eascs.pbafterloan.dao;

import java.math.BigDecimal;
import java.util.Date;

import org.springframework.stereotype.Repository;

import com.eascs.admin.api.form.O2oAfterLoanForm;
import com.eascs.admin.common.dao.BaseO2oDao;
import com.eascs.admin.o2o.loan.entity.O2oLoanapplyEntity;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.common.util.DateUtils;
import com.eascs.common.util.StringUtil;

@Repository
public class O2oPBAfterDao extends BaseO2oDao {

	public PageResult<Object[]> getO2oAfterLoanByPageNew(O2oAfterLoanForm entity) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, entity.getPageSize(), entity.getO2oIndex());
		StringBuffer querySql = new StringBuffer();
		querySql.append("SELECT  * from (");
		querySql.append(" SELECT oll.loanapply_no,oll.bill_code,loan.loan_bank,oll.loan_type,CONCAT(oll.ltd_code,'-',oll.ltd_name) AS ltd_name,opr.province_name,olu.user_full_name,");
		//querySql.append(" CONCAT(LEFT (olu.document_no, 3),'****',RIGHT (olu.document_no, 3)) as document_no ");
		querySql.append(" CAST(ROUND((CASE WHEN(CASE WHEN (oll.bank_amount ) IS NULL THEN oll.ea_mount ELSE(oll.bank_amount)END) IS NULL THEN ");
		querySql.append(" oll.loana_mount ELSE oll.ea_mount END),2)as DECIMAL(10,2))/10000 as loan_amount,DATE_FORMAT(loan.loan_date,'%Y-%m-%d') as loan_date,");
		//querySql.append(" DATE_FORMAT(oll.end_bank_date,'%Y-%m-%d'),oll.operator_name,oll.wf_type,oll.is_place_on_file,");
		querySql.append(" DATE_FORMAT(oll.end_bank_date,'%Y-%m-%d'),oll.operator_name,");
//		querySql.append(" partner.brand_name,partner.partner_name,"); //品牌,合作方
		querySql.append(" (SELECT brand_name from ");
		querySql.append(" (SELECT b.brand_name,tt.partner_code from o2o_brand_info b join ");
		querySql.append(" (SELECT par.brand_id,par.partner_code from o2o_partner_info par");
		querySql.append(" UNION ");
		querySql.append(" SELECT dea.brand_id,dea.pattern_code from o2o_dealer_partner_info  dea");
		querySql.append(" ) tt on b.id=tt.brand_id");
		querySql.append(" ) t where t.partner_code=oll.partner_code");
		querySql.append(" ) as brand_name,oll.partner_name,");
		
		querySql.append(" (SELECT d.item_name FROM PUB_DDITEM d WHERE d.DD_NO = 9944 AND d.item_code = oll.repay_mentway ) as repay_mentway");
		querySql.append(" ,(CASE oll.is_continue_loan WHEN 'Y' THEN '是' WHEN 'N' THEN '否' else '否' END) as is_continue_loan,");
		querySql.append(" oalfc.loan_num as loan_num,DATE_FORMAT(oalfc.the_first_date,'%Y-%m-%d') as first_time, ");
		querySql.append(" oll.payment_time,oll.create_date,opr.province_code ");
		querySql.append(" from o2o_loanapply oll LEFT JOIN o2o_loan_user olu on oll.loanapply_no = olu.loanapply_no and oll.loan_id = olu.loan_id");
		querySql.append(" left join  o2o_bank_loan_data loan on oll.bill_code =loan.bill_code ");
		querySql.append(" LEFT JOIN o2o_platform_regional opr on oll.ltd = opr.platform_id ");
//		querySql.append(" LEFT JOIN (SELECT opi.id,obi.brand_name,opi.partner_name from o2o_brand_info obi join o2o_partner_info opi on obi.id = opi.brand_id) as partner  on oll.partner_id = partner.id");
		querySql.append(" left join o2o_after_loan_for_customer oalfc on oalfc.customer_id= oll.loan_id ");
		querySql.append(" where /**DATA_PERMISSION(oll.ltd)**/ oll.loan_flg='Y' and loan.is_del='N' and olu.sponsor_type is null and olu.spouse_id is null  ");
		
		querySql.append(" and oll.loan_type in ('BP','BP_JX','YC','SG','SC') ");
		querySql.append(" ) tb  where 1=1 ");
		if (!StringUtil.isEmpty(entity.getBillCode())) {
			querySql.append(" AND BILL_CODE LIKE ?");
			sqlPageQuery.addLikeParam(entity.getBillCode());
		}
//		if (!StringUtil.isEmpty(entity.getBankLoan())) {
//			querySql.append(" AND loan.loan_bank = ?");
//			sqlPageQuery.addParam(entity.getBankLoan());
//		}
		if (StringUtil.hasText(entity.getLoanType())) {
			querySql.append(" and loan_type in (");
			String loanTypeA[] = {};
			String loanType = "";
			StringBuffer loanTypes = new StringBuffer();
			if (entity.getLoanType().length() > 4) {
				loanTypeA = entity.getLoanType().split(",");
				for (int i = 0; i < loanTypeA.length; i++) {
					loanTypes.append("'" + loanTypeA[i] + "',");
				}
			} else {
				loanTypes.append("'" + entity.getLoanType() + "',");
			}
			loanType = loanTypes.toString().substring(0, loanTypes.toString().length() - 1);
			querySql.append(loanType);
			querySql.append(")");
		}
		if (!StringUtil.isEmpty(entity.getOperName())) {
			querySql.append(" AND OPERATOR_NAME LIKE ?");
			sqlPageQuery.addLikeParam(entity.getOperName());
		}
		if (!StringUtil.isEmpty(entity.getLoanUserName())) {
			querySql.append(" AND user_full_name LIKE ? ");
			sqlPageQuery.addLikeParam(entity.getLoanUserName());
		}
		if (!StringUtil.isEmpty(entity.getLoanDateStart())) {
			querySql.append(" AND payment_time >= ?");
			sqlPageQuery.addParam(entity.getLoanDateStart());
		}
		if (!StringUtil.isEmpty(entity.getLoanDateEnd())) {
			querySql.append(" AND payment_time < ?");
			sqlPageQuery.addParam(DateUtils.formateStandDate(DateUtils.addDate(DateUtils.formateStandDate(entity.getLoanDateEnd()), 1)));
		}

		if (!StringUtil.isEmpty(entity.getCreateDateStart())) {
			querySql.append(" AND  create_date >= ?");
			sqlPageQuery.addParam(entity.getCreateDateStart());
		}
		if (!StringUtil.isEmpty(entity.getCreateDateEnd())) {
			querySql.append(" AND create_date < ?");
			sqlPageQuery.addParam(DateUtils.formateStandDate(DateUtils.addDate(DateUtils.formateStandDate(entity.getCreateDateEnd()), 1)));
		}
		
		if (!StringUtil.isEmpty(entity.getHostProvince())) {
			querySql.append(" AND  province_code  = ? ");
			sqlPageQuery.addParam(entity.getHostProvince());
		}
		if (!StringUtil.isEmpty(entity.getBrandName())) {
			querySql.append(" AND brand_name like ?");
			sqlPageQuery.addLikeParam(entity.getBrandName());
		}
		if (!StringUtil.isEmpty(entity.getPartnerName())) {
			querySql.append(" AND partner_name like ?");
			sqlPageQuery.addLikeParam(entity.getPartnerName());
		}

		querySql.append(" ORDER BY payment_time desc ");
//		sqlPageQuery.setSumColumnIndexs(new int[] { 7 });
		sqlPageQuery.setQuerySql(querySql.toString());
		return this.findDataWithPage(sqlPageQuery);
	}

	public PageResult<Object[]> getO2oAfterLoanExport(O2oAfterLoanForm entity) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, entity.getPageSize(), entity.getO2oIndex());
		StringBuffer querySql = new StringBuffer();
		querySql.append("SELECT  bill_code,(SELECT d.item_name FROM PUB_DDITEM d WHERE d.DD_NO = 10340 AND d.item_code = loan_bank) as loanname,(SELECT d.item_name FROM PUB_DDITEM d WHERE d.DD_NO = 11321 AND d.item_code = loan_type) as loantype,ltd_name,province_name,user_full_name, loan_amount,loan_date,end_bank_date,operator_name,brand_name,partner_name from (");
		querySql.append(" SELECT oll.loanapply_no,oll.bill_code,loan.loan_bank,oll.loan_type,CONCAT(oll.ltd_code,'-',oll.ltd_name) AS ltd_name,opr.province_name,olu.user_full_name,");
		//querySql.append(" CONCAT(LEFT (olu.document_no, 3),'****',RIGHT (olu.document_no, 3)) as document_no ");
		querySql.append(" CAST(ROUND((CASE WHEN(CASE WHEN (oll.bank_amount ) IS NULL THEN oll.ea_mount ELSE(oll.bank_amount)END) IS NULL THEN ");
		querySql.append(" oll.loana_mount ELSE oll.ea_mount END),2)as DECIMAL(10,2))/10000 as loan_amount,DATE_FORMAT(loan.loan_date,'%Y-%m-%d') as loan_date,");
		//querySql.append(" DATE_FORMAT(oll.end_bank_date,'%Y-%m-%d'),oll.operator_name,oll.wf_type,oll.is_place_on_file,");
		querySql.append(" DATE_FORMAT(oll.end_bank_date,'%Y-%m-%d') as end_bank_date,oll.operator_name,");
//		querySql.append(" partner.brand_name,partner.partner_name,"); //品牌,合作方
		querySql.append(" (SELECT brand_name from ");
		querySql.append(" (SELECT b.brand_name,tt.partner_code from o2o_brand_info b join ");
		querySql.append(" (SELECT par.brand_id,par.partner_code from o2o_partner_info par");
		querySql.append(" UNION ");
		querySql.append(" SELECT dea.brand_id,dea.pattern_code from o2o_dealer_partner_info  dea");
		querySql.append(" ) tt on b.id=tt.brand_id");
		querySql.append(" ) t where t.partner_code=oll.partner_code");
		querySql.append(" ) as brand_name,oll.partner_name,");
		
		querySql.append(" (SELECT d.item_name FROM PUB_DDITEM d WHERE d.DD_NO = 9944 AND d.item_code = oll.repay_mentway ) as repay_mentway");
		querySql.append(" ,(CASE oll.is_continue_loan WHEN 'Y' THEN '是' WHEN 'N' THEN '否' else '否' END) as is_continue_loan,");
		querySql.append(" oalfc.loan_num as loan_num,DATE_FORMAT(oalfc.the_first_date,'%Y-%m-%d') as first_time, ");
		querySql.append(" oll.payment_time,oll.create_date,opr.province_code ");
		querySql.append(" from o2o_loanapply oll LEFT JOIN o2o_loan_user olu on oll.loanapply_no = olu.loanapply_no and oll.loan_id = olu.loan_id");
		querySql.append(" left join  o2o_bank_loan_data loan on oll.bill_code =loan.bill_code ");
		querySql.append(" LEFT JOIN o2o_platform_regional opr on oll.ltd = opr.platform_id ");
//		querySql.append(" LEFT JOIN (SELECT opi.id,obi.brand_name,opi.partner_name from o2o_brand_info obi join o2o_partner_info opi on obi.id = opi.brand_id) as partner  on oll.partner_id = partner.id");
		querySql.append(" left join o2o_after_loan_for_customer oalfc on oalfc.customer_id= oll.loan_id ");
		querySql.append(" where /**DATA_PERMISSION(oll.ltd)**/ oll.loan_flg='Y' and loan.is_del='N' and olu.sponsor_type is null and olu.spouse_id is null  ");
		
		querySql.append(" and oll.loan_type in ('BP','BP_JX','YC','SG','SC') ");
		querySql.append(" ) tb  where 1=1 ");
		if (!StringUtil.isEmpty(entity.getBillCode())) {
			querySql.append(" AND BILL_CODE LIKE ?");
			sqlPageQuery.addLikeParam(entity.getBillCode());
		}
//		if (!StringUtil.isEmpty(entity.getBankLoan())) {
//			querySql.append(" AND loan.loan_bank = ?");
//			sqlPageQuery.addParam(entity.getBankLoan());
//		}
		if (StringUtil.hasText(entity.getLoanType())) {
			querySql.append(" and loan_type in (");
			String loanTypeA[] = {};
			String loanType = "";
			StringBuffer loanTypes = new StringBuffer();
			if (entity.getLoanType().length() > 4) {
				loanTypeA = entity.getLoanType().split(",");
				for (int i = 0; i < loanTypeA.length; i++) {
					loanTypes.append("'" + loanTypeA[i] + "',");
				}
			} else {
				loanTypes.append("'" + entity.getLoanType() + "',");
			}
			loanType = loanTypes.toString().substring(0, loanTypes.toString().length() - 1);
			querySql.append(loanType);
			querySql.append(")");
		}
		if (!StringUtil.isEmpty(entity.getOperName())) {
			querySql.append(" AND OPERATOR_NAME LIKE ?");
			sqlPageQuery.addLikeParam(entity.getOperName());
		}
		if (!StringUtil.isEmpty(entity.getLoanUserName())) {
			querySql.append(" AND user_full_name LIKE ? ");
			sqlPageQuery.addLikeParam(entity.getLoanUserName());
		}
		if (!StringUtil.isEmpty(entity.getLoanDateStart())) {
			querySql.append(" AND payment_time >= ?");
			sqlPageQuery.addParam(entity.getLoanDateStart());
		}
		if (!StringUtil.isEmpty(entity.getLoanDateEnd())) {
			querySql.append(" AND payment_time < ?");
			sqlPageQuery.addParam(DateUtils.formateStandDate(DateUtils.addDate(DateUtils.formateStandDate(entity.getLoanDateEnd()), 1)));
		}

		if (!StringUtil.isEmpty(entity.getCreateDateStart())) {
			querySql.append(" AND  create_date >= ?");
			sqlPageQuery.addParam(entity.getCreateDateStart());
		}
		if (!StringUtil.isEmpty(entity.getCreateDateEnd())) {
			querySql.append(" AND create_date < ?");
			sqlPageQuery.addParam(DateUtils.formateStandDate(DateUtils.addDate(DateUtils.formateStandDate(entity.getCreateDateEnd()), 1)));
		}
		
		if (!StringUtil.isEmpty(entity.getHostProvince())) {
			querySql.append(" AND  province_code  = ? ");
			sqlPageQuery.addParam(entity.getHostProvince());
		}
		if (!StringUtil.isEmpty(entity.getBrandName())) {
			querySql.append(" AND brand_name = ?");
			sqlPageQuery.addParam(entity.getBrandName());
		}
		if (!StringUtil.isEmpty(entity.getPartnerName())) {
			querySql.append(" AND partner_name like ?");
			sqlPageQuery.addLikeParam(entity.getPartnerName());
		}

		querySql.append(" ORDER BY payment_time desc ");
		sqlPageQuery.setQuerySql(querySql.toString());
		return this.findDataWithPage(sqlPageQuery);
	}

	/**
	 * @Description:
	 * 				<p>
	 *               获取正常余额
	 *               </p>
	 * @param billCode
	 *            贷款编号
	 * @param repayDate
	 *            到期日
	 * @param repayStatus
	 *            还款状态
	 * @author CXW
	 * @date 2017年2月23日 上午10:04:30
	 * @version V1.0
	 */
	public String getpaymentsPlan(String billCode) {
		String slq = "select ol.* from o2o_loanapply ol where ol.bill_code=?";
		O2oLoanapplyEntity entity = this.findEntity(slq, O2oLoanapplyEntity.class, billCode);
		BigDecimal amount = new BigDecimal(0);
		// 1.状态为已结清的置为0
		if (entity.getWfType() != null && entity.getWfType().equals("CLO")) {
			return amount.toString();
		}
		// 2.到期日已到的置为0
		Date yestoday = new Date(new Date().getTime());
		if (entity.getSettleDate() != null && yestoday.after(entity.getSettleDate())) {
			return amount.toString();
		}
		String sql = "SELECT ifnull(sum(opp.residual_principal),0) from o2o_payments_plan opp where opp.bill_code=? and opp.payments_date>=NOW()";
		return this.findString(sql, billCode);
	}
}
